package com.rdc.example.dao.impl;

import com.rdc.example.entity.Column;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import static com.rdc.example.util.DBUtil.closeResources;
import static com.rdc.example.util.DBUtil.getConnection;

public class ColumnDaoImpl implements com.rdc.example.dao.ColumnDao {

    @Override
    public boolean insert(Column column) throws Exception {
        // 进行数据库插入操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            //获取连接
            conn = getConnection();
            String sql ="insert into `column` (user_id ,name) values (?,?)";
            pstmt = conn.prepareStatement(sql);
            //设值
            setColumn(pstmt, column);
            //按照影响行数row返回结果
            int row = pstmt.executeUpdate();
            return row > 0;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean deleteById(Integer id) throws Exception {
        // 进行数据库删除操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            //获取连接
            conn = getConnection();
            String sql = "delete from `column` where id=?" ;
            pstmt = conn.prepareStatement(sql);
            //设值
            pstmt.setLong(1, id);
            int row = pstmt.executeUpdate();
            //按照影响行数row返回结果
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            //释放资源
            closeResources(null,pstmt,conn);
        }
    }

    @Override
    public boolean update(Column column) throws Exception {
        // 进行数据库更新操作
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            //获取连接
            conn = getConnection();
            String sql = "update `column` set user_id =?,name=? where id=?";
            pstmt = conn.prepareStatement(sql);
            //设值
            setColumn(pstmt, column);
            pstmt.setInt(3, column.getId());
            int row = pstmt.executeUpdate();
            //按照影响行数row返回结果
            return row > 0;
        }  catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            //释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public Column getColumnById(Integer id) throws Exception {
        // 进行数据库查询操作
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Column column = null;
        try {
            // 注意：column 是 MySQL 保留关键字，需要用反引号包裹
            String sql = "select * from `column` where id=?";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            // 封装 column 数据
            if (rs.next()) {
                column = getColumn(rs);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return column;
    }

    @Override
    public Integer countColumnByUserId(Integer userId) throws Exception {
        // 进行数据库查询操作
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //获取连接
            conn = getConnection();
            String sql = "select count(*) from `column` where user_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return 0;
    }

    @Override
    public List<Column> getColumnListByPageAndUserId(Integer userId, Integer pageBegin, Integer pageSize) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Column> columnList = new ArrayList<>();
        try {
            String sql = "select * from `column` where user_id=? limit ?,?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            pstmt.setInt(2, pageBegin);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Column column = getColumn(rs);
                columnList.add(column);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return columnList;
    }

    @Override
    public List<Column> searchColumnsByTitle(String keyword, Integer pageBegin, Integer pageSize) throws Exception {
        List<Column> columnList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "select * from `column` where name like ? limit ?,?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            pstmt.setInt(2, pageBegin);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                columnList.add(getColumn(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return columnList;
    }

    @Override
    public Integer countSearchColumnsByTitle(String keyword) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            String sql = "select count(*) from `column` where name like ? ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return 0;
    }










    private Column getColumn(ResultSet rs) throws SQLException {
        Column column = new Column();
        column.setId(rs.getInt("id"));
        column.setName(rs.getString("name"));
        column.setUserId(rs.getInt("user_id"));
        column.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        return column;
    }

    private void setColumn(PreparedStatement pstmt, Column column) throws SQLException {
        pstmt.setInt(1, column.getUserId());
        pstmt.setString(2,column.getName());
    }
}
